package cn.stylefeng.guns.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.stylefeng.guns.modular.system.model.Account;
import cn.stylefeng.guns.modular.system.model.User;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

/**
 * Created by handsome on 2016/7/11.
 */
public class ExcellUtil {

//	//当前文件已经存在  
//    private String excelPath = "D:\\exceltest\\comments.xlsx";  
//    //从第几行插入进去  
//    private int insertStartPointer = 3;  
//    //在当前工作薄的那个工作表单中插入这行数据   
//    private String sheetName = "Sheet1"; 
    /*public static void main(String[] args){
        ExcellUtil excelUtil = new ExcellUtil();

        //读取excel数据
        ArrayList<Map<String,String>> result = excelUtil.readExcelToObj("C:\\Users\\handsome\\Desktop\\软文excel\\表\\表\\稿件免费媒体投放链接---上传表.xlsx");
        for(Map<String,String> map:result){
            System.out.println(map);
        }

    }*/
	
	/**
	 * 复制文件
	 * @param file1
	 * @param file2
	 * @return
	 */
	public static Object copyFile(String mainPath,Account user,String type){
	String file1=mainPath+type+".xlsx";
    String file2=mainPath+user.getAccount()+"_"+type+".xlsx";
	File in = new File(file1);
	File out = new File(file2);
	if(!in.exists()){
		System.out.println(in.getAbsolutePath()+"源文件路径错误！！！");
		return ServerResponse.createByErrorMessage("源文件路径错误！！！");
	}
	if(out.exists()){
		out.delete();
//		out.mkdirs();
	}
	FileInputStream fis = null;
	FileOutputStream fos = null;
	try {
		fis = new FileInputStream(in);
//		fos = new FileOutputStream(new File(file2+"\\"+in.getName()));
		fos = new FileOutputStream(new File(file2));
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	}
	int c;
	byte[] b = new byte[1024*5];
	try {
		while((c = fis.read(b))!=-1){
				fos.write(b, 0, c);
			}
		fis.close();
		fos.flush();
		fos.close();
	} catch (IOException e) {
		e.printStackTrace();
		}
	return ServerResponse.createBySuccessMessage("文件初始化成功");
	}
	
	
	/**
	 * 复制文件夹
	 * @param file1
	 * @param file2
	 * @return
	 */
	public static boolean copyFolder(String file1, String file2){
		 File in = new File(file1);
		 File out = new File(file2);
		 if(!in.exists()){
			 System.out.println(in.getAbsolutePath()+"源文件路径错误！！！");
			 return false;
		 }
		 /*else{
		            System.out.println("源文件路径"+in.getAbsolutePath());
		            System.out.println("目标路径"+out.getAbsolutePath());
		        }*/
		 if(!out.exists()){
			 out.mkdirs();
		 }
         File[] file = in.listFiles();
		 FileInputStream fis = null;
		 FileOutputStream fos = null;
		 for(int i=0; i<file.length; i++){
			 if(file[i].isFile())
				try {
					fis = new FileInputStream(file[i]);
					fos = new FileOutputStream(new File(file2+"\\"+file[i].getName()));
				} catch (FileNotFoundException e) {
					e.printStackTrace();
			 }
			 int c;
			 byte[] b = new byte[1024*5];
			 try {
				while((c = fis.read(b))!=-1){
					fos.write(b, 0, c);
				}
				fis.close();
				fos.flush();
				fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		 return false;
		}
	public static void updateExcel(Sheet sheet,int col,int row,String value)throws Exception{
        Row r=sheet.getRow(row);
        Cell cell=r.getCell(col);
//        int type=cell.getCellType();
//        String str1=cell.getStringCellValue();
        //这里假设对应单元格原来的类型也是String类型
        cell.setCellValue(value);
//        System.out.println("单元格原来值为"+str1);
        System.out.println("单元格值被更新为"+value);
    }
	
	public static String getExcellString(Sheet sheet,int col,int row){
		 Row r=sheet.getRow(row);
	     Cell cell=r.getCell(col);
	     return cell.getStringCellValue();
	}
	public static void updateExcel(Sheet sheet,int col,int row,String value,int point)throws Exception{
        Row r=sheet.getRow(row);
        Cell cell=r.getCell(col);
        String str1=cell.getStringCellValue().replace("□"+value, "☑"+value);
        //这里假设对应单元格原来的类型也是String类型
        cell.setCellValue(str1);
//        System.out.println("单元格原来值为"+str1);
        System.out.println("单元格值被更新为"+str1);
    }
	/**
	 * 更新excell的数据
	 * @param exlFile
	 * @param sheetIndex
	 * @param col
	 * @param row
	 * @param value
	 * @throws Exception
	 */
	 public static void updateExcel(File exlFile,int sheetIndex,int col,int row,String value)throws Exception{
	        FileInputStream fis=new FileInputStream(exlFile);
	        Workbook workbook= WorkbookFactory.create(fis);
//	        workbook.
	        Sheet sheet=workbook.getSheetAt(sheetIndex);
	 
	        Row r=sheet.getRow(row);
	        Cell cell=r.getCell(col);
//	        int type=cell.getCellType();
	        String str1=cell.getStringCellValue();
	        //这里假设对应单元格原来的类型也是String类型
	        cell.setCellValue(value);
	        System.out.println("单元格原来值为"+str1);
	        System.out.println("单元格值被更新为"+value);
	 
	        fis.close();//关闭文件输入流
	 
	        FileOutputStream fos=new FileOutputStream(exlFile);
	        workbook.write(fos);
	        fos.close();//关闭文件输出流
	    }
    /**
     * 读取excel数据
     * @param path
     */
    public ArrayList<Map<String,String>> readExcelToObj(String path) {

        Workbook wb = null;
        ArrayList<Map<String,String>> result = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            result = readExcel(wb, 0, 2, 0);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 读取excel文件
     * @param wb
     * @param sheetIndex sheet页下标：从0开始
     * @param startReadLine 开始读取的行:从0开始
     * @param tailLine 去除最后读取的行
     */
    public ArrayList<Map<String,String>> readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        ArrayList<Map<String,String>> result = new ArrayList<Map<String,String>>();
        for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {

            row = sheet.getRow(i);
            Map<String,String> map = new HashMap<String,String>();
            for(Cell c : row) {
                String returnStr = "";

                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                //判断是否具有合并单元格
                if(isMerge) {
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
//                    System.out.print(rs + "------ ");
                    returnStr = rs;
                }else {
//                    System.out.print(c.getRichStringCellValue()+"++++ ");
                    returnStr = c.getRichStringCellValue().getString();
                }
                if(c.getColumnIndex()==0){
                    map.put("id",returnStr);
                }else if(c.getColumnIndex()==1){
                    map.put("base",returnStr);
                }else if(c.getColumnIndex()==2){
                    map.put("siteName",returnStr);
                }else if(c.getColumnIndex()==3){
                    map.put("articleName",returnStr);
                }else if(c.getColumnIndex()==4){
                    map.put("mediaName",returnStr);
                }else if(c.getColumnIndex()==5){
                    map.put("mediaUrl",returnStr);
                }else if(c.getColumnIndex()==6){
                    map.put("newsSource",returnStr);
                }else if(c.getColumnIndex()==7){
                    map.put("isRecord",returnStr);
                }else if(c.getColumnIndex()==8){
                    map.put("recordTime",returnStr);
                }else if(c.getColumnIndex()==9){
                    map.put("remark",returnStr);
                }

            }
            result.add(map);
//            System.out.println();

        }
        return result;

    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }

    /**
     * 判断合并了行
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public boolean isMergedRow(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row == firstRow && row == lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断sheet页中是否含有合并单元格
     * @param sheet
     * @return
     */
    public boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     * @param path excell路径
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     */
    public static void mergeRegion(String path, int firstRow, int lastRow, int firstCol, int lastCol) {
    	File file=new File(path);
		try {
		XSSFWorkbook workbook = returnWorkBookGivenFileHandle(path);  
        XSSFSheet sheet = workbook.getSheetAt(0); 
	    CellRangeAddress m_region =new CellRangeAddress(firstRow-1, (short)(lastRow-1), firstCol-1, (short)(lastCol-1));
        sheet.addMergedRegion(m_region);
        saveExcel(workbook,path);  
		}catch(Exception e){
			e.printStackTrace();
		}
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell){

        if(cell == null) return "";

        if(cell.getCellType() == Cell.CELL_TYPE_STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

            return String.valueOf(cell.getNumericCellValue());

        }
        return "";
    }
    /**
     * 从excel读取内容
     */
    public static void readContent(String fileName)  {
        boolean isE2007 = false;    //判断是否是excel2007格式
        if(fileName.endsWith("xlsx"))
            isE2007 = true;
        try {
            InputStream input = new FileInputStream(fileName);  //建立输入流
            Workbook wb  = null;
            //根据文件格式(2003或者2007)来初始化
            if(isE2007)
                wb = new XSSFWorkbook(input);
            else
                wb = new HSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(0);     //获得第一个表单
            Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
            while (rows.hasNext()) {
                Row row = rows.next();  //获得行数据
                System.out.println("Row #" + row.getRowNum());  //获得行号从0开始
                Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    System.out.println("Cell #" + cell.getColumnIndex());
                    switch (cell.getCellType()) {   //根据cell中的类型来输出数据
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.println(cell.getNumericCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            System.out.println(cell.getStringCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            System.out.println(cell.getCellFormula());
                            break;
                        default:
                            System.out.println("unsuported sell type======="+cell.getCellType());
                            break;
                    }
                }
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
    public static Object excel2Pdf(String mainPath,Account user,String type) throws Exception {
    	String inFilePath=mainPath+user.getAccount()+"_"+type+".xlsx";
    	File in = new File(inFilePath);
    	if(!in.exists()){
    		return ServerResponse.createByErrorMessage("报表文件不存在");
    	}
    	String outFilePath=mainPath+user.getAccount()+"_"+type+".pdf";
	 	final Integer WORD_TO_PDF_OPERAND = 17;
	    final Integer PPT_TO_PDF_OPERAND = 32;
	    final Integer EXCEL_TO_PDF_OPERAND = 0;
        ActiveXComponent ax = null;
        Dispatch excel = null;
        try {
            ComThread.InitSTA();
            ax = new ActiveXComponent("Excel.Application");
            ax.setProperty("Visible", new Variant(false));
            ax.setProperty("AutomationSecurity", new Variant(3)); // 禁用宏
            Dispatch excels = ax.getProperty("Workbooks").toDispatch();

            Object[] obj = new Object[]{ 
                    inFilePath, 
                    new Variant(false),
                    new Variant(false) 
             };
            excel = Dispatch.invoke(excels, "Open", Dispatch.Method, obj, new int[9]).toDispatch();
            
            // 转换格式
            Object[] obj2 = new Object[]{ 
                    new Variant(EXCEL_TO_PDF_OPERAND), // PDF格式=0
                    outFilePath, 
                    new Variant(0)  //0=标准 (生成的PDF图片不会变模糊) ; 1=最小文件
            };
            Dispatch.invoke(excel, "ExportAsFixedFormat", Dispatch.Method,obj2, new int[1]);
            return ServerResponse.createBySuccessMessage("转换文件成功");
        } catch (Exception es) {
        	return ServerResponse.createByErrorMessage("转换文件出现错误，请联系管理员");
            /*es.printStackTrace();
            throw es;*/
        } finally {
            if (excel != null) {
                Dispatch.call(excel, "Close", new Variant(false));
            }
            if (ax != null) {
                ax.invoke("Quit", new Variant[] {});
                ax = null;
            }
            ComThread.Release();
        }
	}
    
   /**
    * 在已有的Excel文件中插入一行新的数据的入口方法 
    * @param excelPath 文件路径
    * @param insertStartPointer 行号
    * @param hight 行高
    * @param cellNum 列数
    * @param wenzi 字体大小
    */
    public static void insertRows(String excelPath,int insertStartPointer,int hight,int cellNum,int wenzi) {  
        XSSFWorkbook wb = returnWorkBookGivenFileHandle(excelPath);  
        XSSFSheet sheet1 = wb.getSheetAt(0); 
        XSSFRow row = createRow(sheet1, insertStartPointer);  
        createCell(row,wb,hight,cellNum,wenzi);  
        saveExcel(wb,excelPath);  
  
    }  
    /** 
     * 保存工作薄 
     * @param wb 
     */  
    private static void saveExcel(XSSFWorkbook wb,String excelPath) {  
        FileOutputStream fileOut;  
        try {  
            fileOut = new FileOutputStream(excelPath);  
            wb.write(fileOut);  
            fileOut.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
  
    }  
    /** 
     * 创建要出入的行中单元格 
     * @param row 
     * @return 
     */  
    private static void createCell(XSSFRow row,XSSFWorkbook wb,int hight,int cellNum,int wenzi) {  
    	CellStyle style = wb.createCellStyle();
    	//下边框 
//	    style.setBorderBottom(BorderStyle.MEDIUM);
	    //下边框颜色
//	    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());//黑色
	    //左边框
	    style.setBorderLeft(BorderStyle.THIN);
	    //左边框颜色
//	    style.setLeftBorderColor(IndexedColors.BLUE.getIndex());//蓝色
	    //右边框
	    style.setBorderRight(BorderStyle.THIN);
	    //右边框颜色
//	    style.setRightBorderColor(IndexedColors.YELLOW.getIndex());//黄色
	    //上边框
	    style.setBorderTop(BorderStyle.THIN);//虚线
	    //上边框颜色
//	    style.setTopBorderColor(IndexedColors.RED.getIndex());//红色
	    style.setWrapText(true);
		 // 水平对齐方式
		 style.setAlignment(HorizontalAlignment.CENTER);
		 // 垂直对齐方式
		 style.setVerticalAlignment(VerticalAlignment.CENTER);
		 // 设置字体
		 Font baseFont = wb.createFont();
		 // 字体类型
		 baseFont.setFontName("宋体");
		 // 字体大小
		 baseFont.setFontHeightInPoints((short) wenzi);
		 style.setFont(baseFont);
    	 row.setHeightInPoints(hight);
    	for(int i=0;i<cellNum;i++){
    		XSSFCell cell = row.createCell(i);
    		cell.setCellStyle(style);
    		cell.setCellValue(" ");  
    	}
        
    }  
   /** 
    * 得到一个已有的工作薄的POI对象 
    * @return 
    */  
    private static XSSFWorkbook returnWorkBookGivenFileHandle(String excelPath) {  
        XSSFWorkbook wb = null;  
        FileInputStream fis = null;  
        File f = new File(excelPath);  
        try {  
            if (f != null) {  
                fis = new FileInputStream(f);  
                wb = new XSSFWorkbook(fis);  
            }  
        } catch (Exception e) {  
            return null;  
        } finally {  
            if (fis != null) {  
                try {  
                    fis.close();  
                } catch (IOException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
        return wb;  
    }  
   /** 
    * 找到需要插入的行数，并新建一个POI的row对象 
    * @param sheet 
    * @param rowIndex 
    * @return 
    */  
    private static XSSFRow createRow(XSSFSheet sheet, Integer rowIndex) {  
        XSSFRow row = null;  
        if (sheet.getRow(rowIndex) != null) {  
            int lastRowNo = sheet.getLastRowNum();  
            sheet.shiftRows(rowIndex, lastRowNo, 1);  
        }  
        row = sheet.createRow(rowIndex);  
        return row;  
    }  
}